package com.wm.blog_common.util.excel;

import com.wm.blog_common.util.CommonUtil;
import com.wm.blog_common.util.StringUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.*;
import java.util.concurrent.*;
import java.util.stream.Collectors;

/***
 * @ClassName: ExcelUtil
 * @Description: excel导出工具类
 * @Author: 半卷流年
 * @Create_time: 16:39 2020-4-1
 */
public class ExcelUtil {

    private ExcelUtil(){}

    /**
     * 多线程,导出excel,默认线程分割大小
     */
    private static Integer EXCEL_EXPORT_DEFAULT_SIZE = 100;

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    public static final int DEFAULT_COLOUMN_WIDTH = 17;


    private static final Long THREAD_THREAD_OUT = 60L;

    private static BlockingQueue queue = new ArrayBlockingQueue(1024);

    private static ThreadPoolExecutor threadPoolExecutor;

    static {
        threadPoolExecutor = new ThreadPoolExecutor(30
                , 40
                , 60L
                , TimeUnit.MILLISECONDS
                , queue
                , new ThreadPoolExecutor.CallerRunsPolicy());
    }


    /**
     * Web 导出excel
     */
    public static void downloadExcelFile(String title, List<String> headMap, List<?> sheetDataListArr,
                                         HttpServletResponse response) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcel(title, headMap, null, sheetDataListArr, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((title + ".xlsx").getBytes(), "utf-8"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * cros
     * Web 导出excel  不带超链接下载导出excel
     */
    public static void downloadExcelFile(String title, List<String> headMap, List<?> sheetDataListArr,
                                         HttpServletResponse response, HttpServletRequest request) {
        downloadExcelFile(title, headMap, null, sheetDataListArr, response, request);
    }




    /**
     * 从excel文件流中读取每行信息，根据第一行表头与clazz类中ExcelField注解名称匹配获取相关列信息,简单解析excel，没有深入生成内置对象
     */
    public static <T> List<T> readFromExcel(InputStream inputStream, Class<T> clazz) throws IOException, IllegalAccessException, InstantiationException {
        return readFromExcel(inputStream, clazz, null);
    }


    /**
     * 读取excel文件,自动生成指定对象集合,如果含有小对象,则渗入生成小对象,excel上传解析
     * @param inputStream  file文件流
     * @param clazz  需要解析成的对象的类型
     * @param headerList  标识有excelField注解的属性名称
     * @param excelRowNum  excel模板中有效数据的行数(不是索引)  注意,这个属性是必须的,并且 excelRowNum - 1 (上一行必须为标题行,否则解析的时间为null)
     * @param <T>
     * @return
     * @throws IOException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    public static <T> List<T> ReadExcelToObject(InputStream inputStream, Class<T> clazz, List<String> headerList, String suffix,Integer excelMax,int excelRowNum) throws IOException, IllegalAccessException, InstantiationException {
        Workbook wb = excel_enum.EXCEL_XLS.suffix.equals(suffix) ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
        excelMax = ObjectUtils.isEmpty(excelMax) ? 65535:excelMax;
        final Integer max = excelMax;
        Map<Field, String> headMap = new IdentityHashMap<>();
        Field[] allFields = FieldReflectionUtil.getAllFields(clazz);
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelDeep.class)) {
                //判断是否有小对象
                Class<?> typeClass = field.getType().newInstance().getClass();
                Field[] typeFields = FieldReflectionUtil.getAllFields(typeClass);
                Arrays.stream(typeFields).filter((Field var1) -> var1.isAnnotationPresent(ExcelField.class)
                        && !Modifier.isStatic(var1.getModifiers())
                        && !Modifier.isFinal(var1.getModifiers())).forEach(var3 -> {
                    setFieldMap(var3, headMap, headerList);
                });
            }
            if (field.isAnnotationPresent(ExcelField.class)) {
                setFieldMap(field, headMap, headerList);
            }
        }
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.rowIterator();
        DataFormatter df = new DataFormatter();
        //记录excel标题行对应的解析类的属性名称集合
        List<String> excelHeaderList = new ArrayList<>();
        List<T> voList = new ArrayList<T>();
        //记录行数
        LinkedList<Integer> rowNumList = new LinkedList<>();
        //记录当前读取的行号(非索引号)
        Integer rowNum = 1;
        int lastRowNum = sheet.getLastRowNum();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            logger.info("row",row.toString());
            //剔除空白行(无格式)
            if (null == row) {
                //处理excel中间的空白行,行号需要累加
                if(rowNum < lastRowNum){
                    ++ rowNum;
                }
                continue;
            }
            short cellTotal = row.getLastCellNum();
            int cellNum = 0;
            //剔除空白行(但是有一定的格式)
            for (Cell c : row) {
                if (c.getCellType() == Cell.CELL_TYPE_BLANK) {
                    cellNum++;
                }
            }
            if (cellTotal == cellNum || -1 == cellTotal) {
                //处理excel中间的空白行,行号需要累加
                if(rowNum < lastRowNum){
                    ++ rowNum;
                }
                continue;
            }
            int rowIndex = row.getRowNum();
            if (rowIndex == excelRowNum - 2) {
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    String headerStr = df.formatCellValue(row.getCell(i)).trim();
                    if (StringUtils.isNotEmpty(headerStr)) {
                        excelHeaderList.add(headerStr);
                    }
                }
            }
            //数据处理从有效数据开始,有效数据以前的全部剔除
            if(rowIndex <= excelRowNum - 2){
                rowNum ++;
                continue;
            }
            rowNumList.addFirst(rowNum);
            T vo = clazz.newInstance();
            //获取小对象集合
            List<Object> deepClassList = new ArrayList<>();
            getDeepObject(clazz, deepClassList);
            for (Iterator<Field> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
                Field field = iter.next();
                String header = headMap.get(field);
                int valueIndex = ArrayUtils.indexOf(excelHeaderList.toArray(), header);
                //记录是否是小对象中的值
                List<Boolean> tempList = new LinkedList<>();
                try {
                    //小对象设置值
                    deepClassList.stream().forEach(var -> {
                        Field[] fields = FieldReflectionUtil.getAllFields(var.getClass());
                        Arrays.stream(fields).filter((Field var2) -> var2.isAnnotationPresent(ExcelField.class)
                                && field.toGenericString().contains(var.getClass().getName())
                                && field.getName().equals(var2.getName())).limit(1).forEach(var3 -> {
                            ((LinkedList<Boolean>) tempList).addFirst(true);
                            var3.setAccessible(true);
                            setInstanceValue(var, var3, df, row, valueIndex);
                        });
                    });
                    if (CollectionUtils.isEmpty(tempList) || !((LinkedList<Boolean>) tempList).getFirst()) {
                        //不是小对象
                        setInstanceValue(vo, field, df, row, valueIndex);
                    }
                } catch (Exception e) {
                    logger.error("设置小对象失败{}:", e.getMessage(), e);
                }
            }
            //设置小对象
            Arrays.stream(allFields).filter((Field field) -> field.isAnnotationPresent(ExcelDeep.class)
                    && !Modifier.isStatic(field.getModifiers())
                    && !Modifier.isFinal(field.getModifiers())).forEach(var -> {
                deepClassList.stream().forEach(var1 -> {
                    if (var.getType().getSimpleName().equals(var1.getClass().getSimpleName())) {
                        var.setAccessible(true);
                        try {
                            //设置大对象的值
                            var.set(vo, var1);
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }
                });
            });
            //设置当前读取的行数
            Arrays.stream(allFields).forEach(var -> {
                var.setAccessible(true);
                try {
                    if (var.isAnnotationPresent(RowNum.class)) {
                        var.set(vo, rowNumList.getFirst());
                    }
                    if (var.isAnnotationPresent(ExcelMax.class) && rowNumList.getFirst() > max) {
                        var.set(vo, false);
                    }
                } catch (IllegalAccessException e) {
                    logger.error("set reading excel row num error rowNumList.first={} :" , rowNumList.getFirst(),  e);
                }
            });
            rowNum ++;
            voList.add(vo);
        }
        return voList;
    }




    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param title            标题行
     * @param headerList       属性-列头
     * @param sheetDataListArr 数据集
     * @param colWidth         列宽 默认 至少17个字节
     * @param out              输出流
     * @throws IllegalArgumentException
     */
    private static void exportExcel(String title, List<String> headerList, HashMap<String, String> linkMap, List<?> sheetDataListArr,
                                    int colWidth, OutputStream out)
            throws IllegalArgumentException {
        SXSSFWorkbook workbook = null;
        try {

            // 声明一个工作薄缓存
            workbook = new SXSSFWorkbook(1000);
            //对刷到磁盘的临时文件压缩
            workbook.setCompressTempFiles(true);
            CreationHelper createHelper = workbook.getCreationHelper();
            // 表头样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints((short) 20);
            titleFont.setBoldweight((short) 700);
            titleStyle.setFont(titleFont);
            // 列头样式
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);
            // 单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            Font cellFont = workbook.createFont();
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            cellStyle.setFont(cellFont);
            // 生成一个(带标题)表格
            Sheet sheet = workbook.createSheet();
            //绘图器,用来导入图片的
            Drawing patriarch = sheet.createDrawingPatriarch();


            //1000行刷到磁盘
            ((SXSSFSheet) sheet).setRandomAccessWindowSize(-1);
            // 设置列宽 至少字节数
            int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;
            if (sheetDataListArr != null && !sheetDataListArr.isEmpty()) {
                Class<?> sheetClass = sheetDataListArr.get(0).getClass();
                Map<String, String> headMap = new LinkedHashMap<>();
                Field[] allFields = FieldReflectionUtil.getAllFields(sheetClass);
                if (allFields != null && allFields.length > 0) {
                    for (Field field : allFields) {
                        if (field.isAnnotationPresent(ExcelField.class)) {
                            if (headerList != null && headerList.size() != 0) {
                                for (String header : headerList) {
                                    if (field.getName().equals(header)) {
                                        headMap.put(field.getName(), field.getAnnotation(ExcelField.class).name());
                                    }
                                }
                            } else {
                                headMap.put(field.getName(), field.getAnnotation(ExcelField.class).name());
                            }
                        }
                    }
                }
                int[] arrColWidth = new int[headMap.size()];
                // 产生表格标题行,以及设置列宽
                String[] headers = new String[headMap.size()];
                List<Field> fields = new ArrayList<>();

                int ii = 0;
                for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
                    String fieldName = iter.next();
                    if (allFields != null && allFields.length > 0) {
                        for (Field field : allFields) {
                            if (Modifier.isStatic(field.getModifiers()) || Modifier.isAbstract(field.getModifiers()) || Modifier.isNative(field.getModifiers())
                                    || Modifier.isFinal(field.getModifiers())) {
                                continue;
                            }
                            if (field.getName().equals(fieldName)) {
                                fields.add(field);
                            }
                        }
                    }
                    headers[ii] = headMap.get(fieldName);
                    int bytes = fieldName.getBytes().length;
                    arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
                    sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
                    ii++;
                }
                int rowIndex = 0;

                rowIndex = initExcel(rowIndex,sheet,title,titleStyle,workbook,headers,headerStyle,headMap);
                List<? extends List<?>> list = CommonUtil.splitList(sheetDataListArr, EXCEL_EXPORT_DEFAULT_SIZE);
                //线程计数
                CountDownLatch latch = new CountDownLatch(list.size());
                for (int i = 1; i <= list.size() ; i++) {
                    threadPoolExecutor.submit(new PoiExcelTask(sheet,workbook,list.get(i-1),fields,rowIndex,createHelper,linkMap,cellStyle,latch,patriarch));
                    rowIndex = incrRowNum(rowIndex,EXCEL_EXPORT_DEFAULT_SIZE,list);
                }
                try {
                    //阻塞(最多60秒)，直到计数器的值为0
                    latch.await(THREAD_THREAD_OUT, TimeUnit.SECONDS);
                } catch (InterruptedException e) {
                    logger.error("excel线程执行异常:{}",e.getMessage(),e);
                }
            }
            // 自动调整宽度 todo
            /*
             * for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); }
             */
            try {
                workbook.write(out);
                out.close();
                workbook.dispose();
            } catch (IOException e) {
                logger.warn("导出Excel异常：{}", e.getLocalizedMessage());
            }
        } finally {
            if (null != workbook) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    logger.warn("导出Excel,关闭workbook异常：{}", e.getLocalizedMessage());
                }
            }
        }

    }

    /**
     * excel行数增加,同步锁定
     * @param rowIndex
     * @param num
     * @return
     */
    private static synchronized int incrRowNum(int rowIndex,Integer num,List<? extends List<?>> list){
        if(list.size() == 1){
            rowIndex += list.get(0).size();
        }else{
            rowIndex += num;
        }
        return rowIndex;
    }



    /**
     * cros
     * Web 导出excel,带超链接
     */
    private static void downloadExcelFile(String title, List<String> headMap, HashMap<String, String> linkMap, List<?> sheetDataListArr,
                                          HttpServletResponse response, HttpServletRequest request) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcel(title, headMap, linkMap, sheetDataListArr, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            //            response.setHeader("Access-Control-Allow-Methods", "POST,GET,OPTIONS,DELETE,PUT");
            response.setHeader("Access-Control-Max-Age", "3600");
            String originHeader = request.getHeader("Origin");

            //response.setHeader("Access-Control-Allow-Origin", "*");
            //            response.setHeader("Access-Control-Allow-Credentials", "true");
            //response.setHeader("Access-Control-Allow-Headers", "Content-Type, Access-Control-Allow-Headers, Authorization," + X_AUTH_SESSIONID);
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private static <T> List<T> readFromExcel(InputStream inputStream, Class<T> clazz, List<String> headerList) throws IOException, IllegalAccessException, InstantiationException {
        HSSFWorkbook wb = new HSSFWorkbook(inputStream);
        Map<Field, String> headMap = new LinkedHashMap<>();
        Field[] allFields = FieldReflectionUtil.getAllFields(clazz);
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelField.class)) {
                if (headerList != null && headerList.size() != 0) {
                    for (String header : headerList) {
                        if (field.getName().equals(header)) {
                            headMap.put(field, field.getAnnotation(ExcelField.class).name());
                        }
                    }
                } else {
                    headMap.put(field, field.getAnnotation(ExcelField.class).name());
                }
            }
        }
        HSSFSheet sheet = wb.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.rowIterator();
        DataFormatter df = new DataFormatter();
        List<String> excelHeaderList = new ArrayList<>();
        List<T> voList = new ArrayList<T>();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    String headerStr = df.formatCellValue(row.getCell(i)).trim();
                    if (StringUtils.isNotEmpty(headerStr)) {
                        excelHeaderList.add(headerStr);
                    }
                }
                continue;
            }
            T vo = clazz.newInstance();
            for (Iterator<Field> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
                Field field = iter.next();
                String header = headMap.get(field);
                int valueIndex = ArrayUtils.indexOf(excelHeaderList.toArray(), header);
                try {
                    field.set(vo, StringUtil.castObjectToClazz(field.getType(), df.formatCellValue(row.getCell(valueIndex))));
                } catch (Exception e) {
                }
            }
            voList.add(vo);
        }
        return voList;
    }




    /**
     * 给反射对象设置值
     */
    private static void setInstanceValue(Object vo, Field field, DataFormatter df, Row row, int valueIndex) {
        try {
            field.setAccessible(true);
            //判断是否需要转换为日期
            if (field.isAnnotationPresent(ExcelFormat.class)) {
                String pattern = field.getAnnotation(ExcelFormat.class).pattern();
                if (DateUtil.isCellDateFormatted(row.getCell(valueIndex))) {
                    Date date = row.getCell(valueIndex).getDateCellValue();
                    field.set(vo, date);
                }
            } else if (field.isAnnotationPresent(ExcelStringToStatus.class)) {
                String excelValue = df.formatCellValue(row.getCell(valueIndex));
                //是否需要转换为Integer状态值
                String[] values = field.getAnnotation(ExcelStringToStatus.class).values();
                if (CollectionUtils.isNotEmpty(Arrays.asList(values))) {
                    Arrays.stream(values).forEach(var -> {
                        //得到如---  "0:失效"
                        String[] varArr = var.split(":");
                        if (StringUtils.isNotEmpty(varArr[1]) && varArr[1].trim().equals(excelValue)) {
                            try {
                                field.set(vo, Integer.parseInt(varArr[0].trim()));
                            } catch (IllegalAccessException e) {
                                logger.error("<setInstanceValue> reflect set value error name={}:" , field.getName(),  e);
                            }
                        }
                    });
                }
            } else {
                //过滤没有在excel单元格中的字段
                if (-1 != valueIndex) {
                    field.set(vo, StringUtil.castObjectToClazz(field.getType(), df.formatCellValue(row.getCell(valueIndex)).trim()));
                }
            }
        } catch (IllegalAccessException e) {
            logger.error("setInstanceValue reflect set value error name={} ,errorMsg={}:" , field.getName(), e.getMessage(), e);
        }

    }

    /**
     * 获取小对象
     * @param clzz
     * @param deepClassList
     * @return
     */
    private static List<Object> getDeepObject(Class<?> clzz, List<Object> deepClassList) {
        if (null != clzz) {
            Field[] fields = FieldReflectionUtil.getAllFields(clzz);
            Arrays.stream(fields).filter((Field field) -> field.isAnnotationPresent(ExcelDeep.class)).forEach(var -> {
                try {
                    deepClassList.add(var.getType().newInstance());
                } catch (InstantiationException | IllegalAccessException e) {
                    logger.error("<getDeepObject> get object from class error SimpleName={} , errorMsg={} ...:" , clzz.getSimpleName(), e.getMessage(), e);
                }
            });
        }
        return deepClassList;
    }


    private static void setFieldMap(Field field, Map<Field, String> headMap, List<String> headerList) {
        if (field.isAnnotationPresent(ExcelField.class)) {
            if (headerList != null && headerList.size() != 0) {
                for (String header : headerList) {
                    if (field.getName().equals(header)) {
                        headMap.put(field, field.getAnnotation(ExcelField.class).name());
                    }
                }
            } else {
                headMap.put(field, field.getAnnotation(ExcelField.class).name());
            }
        }
    }


    /**
     * 初始化excel
     * @param rowIndex
     * @param sheet
     * @param title
     * @param titleStyle
     * @param workbook
     * @param headers
     * @param headerStyle
     * @param headMap
     * @return
     */
    private static Integer initExcel(Integer rowIndex,Sheet sheet,String title,CellStyle titleStyle,Workbook workbook,String[] headers,CellStyle headerStyle,Map<String, String> headMap){
        if (rowIndex == 100000 || rowIndex == 0) {
            if (rowIndex != 0) {
                // 如果数据超过了，则在第二页显示
                sheet = workbook.createSheet();
            }
            // 表头 rowIndex=0
            Row titleRow = sheet.createRow(0);
            titleRow.createCell(0).setCellValue(title);
            titleRow.getCell(0).setCellStyle(titleStyle);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
            // 列头 rowIndex =1
            Row headerRow = sheet.createRow(1);
            for (int i = 0; i < headers.length; i++) {
                headerRow.createCell(i).setCellValue(headers[i]);
                headerRow.getCell(i).setCellStyle(headerStyle);
            }
            // 数据内容从 rowIndex=2开始
            rowIndex = 2;
        }
        return rowIndex;
    }


    /**
     * 模板文件下载,返回给浏览器直接下载
     */
    public static void downTemplate(InputStream inputStream,HttpServletResponse response,String title) throws IOException {
       //创建workBook
        Workbook workbook = new XSSFWorkbook(inputStream);
        ServletOutputStream outputStream = response.getOutputStream();
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
        inputStream.close();
    }





    /**
     *
     */
    public enum excel_enum {
        /**
         *
         */
        EXCEL_XLS("application/vnd.ms-excel", ".xls"),
        EXCEL_XLSX("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ".xlsx");

        excel_enum(String mineType, String suffix) {
            this.suffix = suffix;
            this.mineType = mineType;
        }

        /**
         *
         */
        private String suffix;
        private String mineType;

        public static String getSuffix(String mineType) {
            List<excel_enum> list = Arrays.stream(excel_enum.values()).filter((excel_enum excelEnum) -> StringUtils.isNotEmpty(mineType)
                    && excelEnum.mineType.equals(mineType)).limit(1).collect(Collectors.toList());
            if (CollectionUtils.isNotEmpty(list)) {
                return list.get(0).suffix;
            }
            return null;
        }
    }






}
